DELETE /employees

PUT /employees/
{
  "mappings" : {
      "properties" : {
        "ip": {"type": "ip"},
        "joinedDate": {"type": "date", "format": "yyyy-MM-dd"},
        "age" : {"type" : "integer"},
        "gender" : {"type" : "keyword"},
        "name" : {"type" : "keyword"},
        "salary" : {"type" : "integer"},
        "job" : {
          "type" : "text",
          "fields" : {
            "keyword" : {"type" : "keyword","ignore_above" : 50}
          }
        }
      }
    }
}
// 复制到 kibana 中执行导入数据
PUT /employees/_bulk
{ "index" : {  "_id" : "1" } }
{ "ip": ["192.168.1.10", "192.168.10.100"], "joinedDate": "2010-05-01", "name" : "Emma","age":32,"job":"Product Manager","gender":"female","salary":35000 }
{ "index" : {  "_id" : "2" } }
{ "ip": ["192.168.1.20", "192.168.10.101"], "joinedDate": "2000-01-01", "name" : "Underwood","age":41,"job":"Dev Manager","gender":"male","salary": 50000}
{ "index" : {  "_id" : "3" } }
{ "ip": "192.168.1.30","joinedDate": "2019-01-01", "name" : "Tran","age":25,"job":"Web Designer","gender":"male","salary":18000 }
{ "index" : {  "_id" : "4" } }
{ "ip": "192.168.1.31","joinedDate": "2019-01-01", "name" : "Rivera","age":26,"job":"Web Designer","gender":"female","salary": 22000}
{ "index" : {  "_id" : "5" } }
{ "ip": "192.168.1.41","joinedDate": "2020-02-01", "name" : "Rose","age":25,"job":"QA","gender":"female","salary":18000 }
{ "index" : {  "_id" : "6" } }
{ "ip": "192.168.1.42","joinedDate": "2010-11-01", "name" : "Lucy","age":31,"job":"QA","gender":"female","salary": 25000}
{ "index" : {  "_id" : "7" } }
{ "ip": "192.168.1.1","joinedDate": "2017-12-01", "name" : "Byrd","age":27,"job":"QA","gender":"male","salary":20000 }
{ "index" : {  "_id" : "8" } }
{ "ip": "192.168.1.21","joinedDate": "2020-01-01", "name" : "Foster","age":27,"job":"Java Programmer","gender":"male","salary": 20000}
{ "index" : {  "_id" : "9" } }
{ "ip": "192.168.1.22","joinedDate": "2015-01-01", "name" : "Gregory","age":32,"job":"Java Programmer","gender":"male","salary":22000 }
{ "index" : {  "_id" : "10" } }
{ "ip": "192.168.1.23","joinedDate": "2020-01-01", "name" : "Bryant","age":20,"job":"Java Programmer","gender":"male","salary": 9000}
{ "index" : {  "_id" : "11" } }
{ "ip": "192.168.1.24","joinedDate": "2011-12-01", "name" : "Jenny","age":36,"job":"Java Programmer","gender":"female","salary":38000 }
{ "index" : {  "_id" : "12" } }
{ "ip": "192.168.1.25","joinedDate": "2017-10-01", "name" : "Mcdonald","age":31,"job":"Java Programmer","gender":"male","salary": 32000}
{ "index" : {  "_id" : "13" } }
{ "ip": "192.168.1.26","joinedDate": "2020-01-01", "name" : "Jonthna","age":30,"job":"Java Programmer","gender":"female","salary":30000 }
{ "index" : {  "_id" : "14" } }
{ "ip": "192.168.1.27","joinedDate": "2019-04-01", "name" : "Marshall","age":32,"job":"Javascript Programmer","gender":"male","salary": 25000}
{ "index" : {  "_id" : "15" } }
{ "ip": "192.168.1.28","joinedDate": "2018-08-01", "name" : "King","age":33,"job":"Java Programmer","gender":"male","salary":28000 }
{ "index" : {  "_id" : "16" } }
{ "ip": "192.168.10.11","joinedDate": "2020-03-01", "name" : "Mccarthy","age":21,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "index" : {  "_id" : "17" } }
{ "ip": "192.168.10.12","joinedDate": "2019-08-01", "name" : "Goodwin","age":25,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "index" : {  "_id" : "18" } }
{ "ip": "192.168.10.13","joinedDate": "2016-01-01", "name" : "Catherine","age":29,"job":"Javascript Programmer","gender":"female","salary": 20000}
{ "index" : {  "_id" : "19" } }
{ "ip": "192.168.1.100", "joinedDate": "2014-09-01", "name" : "Boone","age":30,"job":"DBA","gender":"male","salary": 30000}
{ "index" : {  "_id" : "20" } }
{ "ip": "192.168.1.101", "joinedDate": "2019-06-01", "name" : "Kathy","age":29,"job":"DBA","gender":"female","salary": 20000}


// 对全部文档聚合, select * from employees group by job.keyword;
GET employees/_search
{
    "size": 0,
    "aggs": {
        "jobs": {
            "terms": {
                // 排序
                // "order": [
                //     {"_count": "asc"}, // "数量升序"
                //     {"_key": "desc"} // key倒叙
                // ],
                "field": "job.keyword"
            }
        }
    }
}

GET _sql
{
    "query": "select * from employees"
}

// 分桶聚合: 对query出来的内容聚合
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {
                "gte": 20
            }
        }
    },
    "aggs": {
        "jobs": {
            "terms": {
                "field": "job.keyword"
            }
        }
    }
}

// 分桶聚合: range
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {
                "gte": 20
            }
        }
    },
    "aggs": {
        "范围": {
            "range": {
                "field": "salary",
                // "keyed": true, // 把结果的buckets由数组改为对象, 并且生成的 bucket_key 作为buckets对象的key
                "ranges": [
                    {"to": 10000, "key": "低于10000"}, // 可以指定key, 可以不指定
                    {"from":10000, "to": 20000},
                    {"from":20000, "to": 30000},
                    {"from":30000, "to": 40000},
                    {"from":40000}
                ]
            }
        }
    }
}

// 分桶聚合: IP range
GET employees/_search
{
    "size": 0,
    "aggs": {
        "192.168.1.0/24": {
            "ip_range": {
                "field": "ip",
                "ranges": [
                    {"to": "192.168.1.20", "from": "192.168.1.1"},
                    {"to": "192.168.1.255", "from": "192.168.1.20"}
                ]
            }
        }
    }
}
// 分桶聚合: IP range
GET employees/_search
{
    "size": 0,
    "aggs": {
        "网段": {
            "ip_range": {
                "field": "ip",
                "ranges": [
                    {"from": "192.168.1.1", "to": "192.168.1.255"},
                    {"from": "192.168.10.1", "to": "192.168.10.255"}
                ]
            }
        }
    }
}

// 分桶聚合: histogram
// range 可以指定key, 这个key由计算公式算出 bucket_key = Math.floor((value - offset) / interval) * interval + offset
GET employees/_search
{
    "size": 0,
    "aggs": {
        "递增": {
            "histogram": {
                "field": "salary",
                // "keyed": true, // 把结果的buckets由数组改为对象, 并且生成的 bucket_key 作为buckets对象的key
                // "min_doc_count": 1, // 可以 过滤 最小文档的数量
                // "missing": 0 , // missing value处理, 默认是忽略
                // "extended_bounds": {"min": -10000, "max": 100000}, // 扩展边界, 不是 过滤
                "interval": 10000
            }
        }
    }
}


// 指标聚合: avg 平均
GET employees/_search
{
    "size": 0,
    "aggs": {
        "平均": {
            "avg": {
                "field": "salary"
            }
        }
    }
}

// 指标聚合: max
GET employees/_search
{
    "size": 0,
    "aggs": {
        "最大值": {
            "max": {
                "field": "salary"
            }
        }
    }
}

// 指标聚合: min
GET employees/_search
{
    "size": 0,
    "aggs": {
        "最小值": {
            "min": {
                "field": "salary"
            }
        }
    }
}

GET employees/_search
{
    "size": 0,
    "aggs": {
        "最小值": {
            "min": {
                "field": "salary"
            }
        },
        "最大值": {
            "max": {
                "field": "salary"
            }
        },
        "平均值": {
            "avg": {
                "field": "salary"
            }
        }
    }
}

// 指标聚合: 包含 数量, 最大值, 最小值, 平均, 求和
GET employees/_search
{
    "size": 0,
    "aggs": {
        "状态": {
            "stats": {
                "field": "salary"
            }
        }
    }
}

// 二次聚合: 二次分桶聚合
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {
                "gte": 20
            }
        }
    },
    "aggs": {
        "jobs": {
            "terms": {
                "field": "job.keyword"
            },
            "aggs": {
                "性别": {
                    "terms": {"field": "gender"}
                } 
            }
        }
    }
}

// 二次聚合: 分桶聚合 + 指标聚合
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {
                "gte": 20
            }
        }
    },
    "aggs": {
        "jobs": {
            "terms": {
                "field": "job.keyword"
            },
            "aggs": {
                "gender": {
                    "stats": {"field": "salary"}
                }
            }
        }
    }
}

// 二次聚合: date_histogram + stats
GET employees/_search
{
    "size": 0,
    "aggs": {
        "time_year": {
            "date_histogram": {
                "field": "joinedDate",
                "calendar_interval": "year",
                "format": "yyyy" // key 的时间格式
            },
            "aggs": {
                "stat": {
                    "stats": {"field": "salary"}
                }
            }
        }
    }
}

// 上面有的聚合的数量为0, 这里把为0的过滤掉
// 二次聚合: date_histogram + stats + bucket selector
GET employees/_search
{
    "size": 0,
    "aggs": {
        "time_year": {
            "date_histogram": {
                "field": "joinedDate",
                "calendar_interval": "year",
                "format": "yyyy" // key 的时间格式
            },
            "aggs": {
                "stat": {
                    "stats": {"field": "salary"}
                },
                "res_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "cnt": "stat.count"
                            // "cnt": "_count" // 如果需要指定doc_count, 可以使用 _count
                            },
                        "script": "params.cnt > 0"                 
                    }                    
                }
            }
        }
    }
}

// 二次聚合: 分桶聚合 + 指标聚合 + 单值指标聚合数据排序
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {
                "gte": 20
            }
        }
    },
    "aggs": {
        "jobs": {
            "terms": {
                "field": "job.keyword",
                "order": [
                    {"avgSalary": "desc"}
                ]
            },
            "aggs": {
                "avgSalary": {
                    "avg": {"field": "salary"}
                }
            }
        }
    }
}
// 二次聚合: 分桶聚合 + 指标聚合 + 多值指标聚合数据排序
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {
                "gte": 20
            }
        }
    },
    "aggs": {
        "jobs": {
            "terms": {
                "field": "job.keyword",
                "order": [
                    {"statsSalary.sum": "desc"}
                ]
            },
            "aggs": {
                "statsSalary": {
                    "stats": {"field": "salary"}
                }
            }
        }
    }
}

// filter
GET employees/_search
{
    "size": 0,
    "aggs": {
        "大于35": {
            "filter": {
                "range": {
                    "age": {"from": 35}
                }
            },
            "aggs": {
                "工作": {
                    "terms": {
                        "field": "job.keyword"
                    }
                }
            }
        },
        "全部工作": {
            "terms": {
                "field": "job.keyword"
            }
        }
    }
}

// post_filter: 找出所有的job类型, 还能找到聚合后符合条件的结果
GET employees/_search
{
    "aggs": {
        "jobs": {
            "terms": {
                "field": "job.keyword"
            }
        }
    },
    "post_filter": {
        "match": {
            "gender": "male"
        }
    }
}

// global: 在由global的aggs中, 会忽略query中的条件
GET employees/_search
{
    "size": 0,
    "query": {
        "range": {
            "age": {"gte": 40}
        }
    },
    "aggs": {
        "jobs": {
            "terms": {"field": "job.keyword"}
        },
        "all": {
            "global": {},
            "aggs": {
                "平均薪资": {
                    "avg": {
                        "field": "salary"
                    }
                }
            }
        }
    }
}

